﻿Imports System.Text.RegularExpressions
Imports System.Data.OleDb
Imports System.Data.SqlClient
Public Class frmGestionListeNoire
    Dim idClient As Integer
    Dim dateDebut As DateTime
    Dim messageErreur As String
    Dim dbClientsNoires As New cls_taco


    Private Sub btnRechercheDetaillee_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRechercheDetaillee.Click
        If panRecherche.Visible = True Then
            panRecherche.Visible = False
            lstResultat.Height += panRecherche.Height
            lstResultat.Top -= panRecherche.Height
            btnRechercheDetaillee.Text = "/\"
        Else
            panRecherche.Visible = True
            lstResultat.Height -= panRecherche.Height
            lstResultat.Top += panRecherche.Height
            btnRechercheDetaillee.Text = "\/"
        End If

    End Sub

    Private Sub btnQuitter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Close()
    End Sub

    
    Public Function validerDonnee()
        Return validerTel(txtTel.Text, messageErreur, True) = True And validerNom(txtNom.Text, messageErreur, True) _
        And validerNom(txtPrenom.Text, messageErreur, True) And validerDuree(txtNbJours.Text, messageErreur, True)
    End Function



    Private Sub btnRechercher_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRechercher.Click
        'If validerChamp() = False Then
        'MsgBox("Veuillez spécifier au moins un critère de recherche")
        messageErreur = "Erreur: "
        If validerDonnee() Then

            ' MsgBox(creerRequete)
            remplirListView()
            btnModifier.Enabled = False
            btnSupprimer.Enabled = False

        Else
            MsgBox(messageErreur)
        End If

    End Sub




    Public Function validerChamp() As Boolean
        If txtTel.Text = Nothing And cboVille.Text = Nothing And txtPrenom.Text = Nothing _
        And txtNom.Text = Nothing Then
            Return False
        End If

        Return True
    End Function


    Private Sub btnRaz_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRaz.Click
        txtNom.Text = Nothing

        txtPrenom.Text = Nothing
        cboProvince.Text = cboProvince.Items(0)
        cboVille.Text = cboVille.Items(0)
        txtTel.Text = Nothing

        btnRechercher.PerformClick()
    End Sub

    Private Sub btnAjouter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAjouter.Click

        frmModifListeNoire.ShowDialog(Nothing, Nothing)

    End Sub

    Sub remplirListView()
        Dim etat As String
        Dim dateDebutListe As DateTime
        Dim dateFinListe As DateTime
        btnSupprimer.Enabled = False
        btnModifier.Enabled = False
        'créé la connection à la base de données
        dbClientsNoires = New cls_taco

        dbClientsNoires.defDataset(creerRequete(), "tblClientsNoires")

        'créé le dataSet
        'remplit le dataSet avec la requête SQL du dataAdapter
        'vide la listView
        lstResultat.Clear()

        'créé une colonne dans la listView pour chaque élément de la requête SQL
        For ctr = 0 To dbClientsNoires.ds.Tables("tblClientsNoires").Columns.count - 1
            lstResultat.Columns.Add(dbClientsNoires.ds.Tables("tblClientsNoires").Columns(ctr).columnName, 130)

            If lstResultat.Columns(ctr).text = "noClient" Then lstResultat.Columns(ctr).Width = 0
        Next
        lstResultat.Columns.Add("État", 69)

        'créé un élément dans le listView pour chaque enregistrement retourné par la requête SQL
        For Each client As DataRow In dbClientsNoires.ds.Tables("tblClientsNoires").Rows

            Dim LI As New ListViewItem
            LI.Text = client.Item(0)

            For ctr As Integer = 1 To dbClientsNoires.ds.Tables("tblClientsNoires").Columns.Count - 1

                If dbClientsNoires.ds.Tables("tblClientsNoires").Columns(ctr).columnName = "duree" Then
                    LI.SubItems.Add(nvl(client.Item(ctr) & " jours", ""))
                Else
                    LI.SubItems.Add(nvl(client.Item(ctr), ""))
                End If

                
            Next
            dateDebutListe = client.Item("dateDebut")
            dateFinListe = dateDebutListe.AddDays(client.Item("duree"))
            If Date.Compare(FormatDateTime(dateFinListe, DateFormat.ShortDate), FormatDateTime(Now, DateFormat.ShortDate)) <= 0 Then etat = "Terminé" Else etat = "Non terminé"
            LI.SubItems.Add(etat)

            lstResultat.Items.Add(LI)

        Next


    End Sub

    Public Function creerRequete() As OleDbCommand

        Dim requete As String = "SELECT c.nom AS Nom, c.prenom AS Prenom, c.telephone as Telephone, ln.dateDebut, ln.duree, c.noClient FROM tblClients c, tblListeNoire ln WHERE c.noClient = ln.noClient "
        Dim maCommande As New OleDbCommand

        If cboProvince.Text <> "Tous" And cboProvince.Text <> "" Then
            requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " c.CodeProvince = (select CodeProvince from tblProvinces where nomProvince = @province)"
            maCommande.Parameters.Add("@province", OleDbType.BSTR, 50).Value = cboProvince.Text
        End If

        If cboVille.Text <> "Tous" And cboVille.Text <> "" Then
            ' requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " ville='Trois-Rivières'"
            requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " c.ville = @ville"
            maCommande.Parameters.Add("@ville", OleDbType.BSTR, 50).Value = cboVille.Text
        End If

        If txtTel.Text <> "" Then
            requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " c.telephone LIKE @telephone"
            maCommande.Parameters.Add("@telephone", OleDbType.BSTR, 50).Value = "%" & txtTel.Text & "%"
        End If
        If txtNom.Text <> "" Then
            requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " c.nom LIKE @nom"
            maCommande.Parameters.Add("@nom", OleDbType.BSTR, 50).Value = "%" & txtNom.Text & "%"
        End If
        If chkEntreprise.Checked = True Then
            requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " c.noEntreprise is not null"

        End If
        If txtPrenom.Text <> "" Then
            requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " c.prenom LIKE @prenom"
            maCommande.Parameters.Add("@prenom", OleDbType.BSTR, 50).Value = "%" & txtPrenom.Text & "%"
        End If

        If chkDuree.Checked = True Then
            requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " ln.duree = @duree"
            maCommande.Parameters.Add("@prenom", OleDbType.Integer, 50).Value = CInt(txtNbJours.Text)
        End If

        If chkDateDeb.Checked = True Then
            requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " ln.dateDebut = @dateDebut"
            maCommande.Parameters.Add("@prenom", OleDbType.DBDate, 50).Value = dtDebut.Value
        End If


        If chkActif.Checked = True Then
            requete = requete & IIf(requete.Contains("WHERE"), " AND", "WHERE") & " ln.dateDebut+ln.duree >= now"
            
        End If

        maCommande.CommandText = requete
        'maCommande.Parameters.Add(New OleDb.OleDbParameter("@province", cboProvince.Text))
        ' MsgBox(maCommande.Parameters("@province").Value)
        Return maCommande
        ' 
        '"SELECT type AS Type, marque AS Marque, modele As Modèle " & _
        '  "FROM tblVehicules")
    End Function



    Private Sub cboProvince_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboProvince.SelectedIndexChanged

    End Sub

    Private Sub cboProvince_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboProvince.SelectedValueChanged


    End Sub

    Private Sub cboProvince_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboProvince.SelectionChangeCommitted
        remplirListCbo("ville", "Tous", cboVille, " WHERE v.CodeProvince = p.CodeProvince AND p.nomProvince = '" & cboProvince.SelectedItem & "'")
    End Sub

    Private Sub cboVille_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboVille.SelectedIndexChanged

    End Sub

    Private Sub cboVille_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboVille.SelectionChangeCommitted

    End Sub

    Private Sub btnSupprimer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSupprimer.Click
        Dim clientCourant As Integer
        Dim dateDebutCourante As DateTime

        clientCourant = CInt(lstResultat.SelectedItems(0).SubItems(5).Text)
        dateDebutCourante = CDate(lstResultat.SelectedItems(0).SubItems(3).Text)


        Dim suppClients As New cls_taco
        suppClients.defDataset("select * from tblListeNoire where noClient = " & clientCourant & " and dateDebut = #" & FormatDateTime(dateDebutCourante, DateFormat.ShortDate) & "#", "tblClientsNoires")

        'probleme incapable de modifier ceci
        For Each client As DataRow In suppClients.ds.Tables("tblClientsNoires").Rows

            If lstResultat.SelectedItems(0).SubItems(6).Text = "Non terminé" Then

                client.Item("duree") = DateDiff(DateInterval.Day, dateDebutCourante, Now.Date)

            End If
        Next

        suppClients.update("tblClientsNoires")

        remplirListView()
    End Sub



    Private Sub lstResultat_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles lstResultat.MouseClick
        If lstResultat.SelectedItems(0).SubItems(6).Text = "Non terminé" Then
            btnSupprimer.Enabled = True
        Else
            btnSupprimer.Enabled = False
        End If

        'btnSupprimer.Enabled = True
        btnModifier.Enabled = True
    End Sub





    Private Sub btnModifier_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnModifier.Click
        idClient = lstResultat.SelectedItems(0).SubItems(5).Text
        dateDebut = lstResultat.SelectedItems(0).SubItems(3).Text
        frmModifListeNoire.ShowDialog(idClient, dateDebut)

    End Sub



    Private Sub frmGestionListeNoire_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        btnSupprimer.Enabled = False
        btnModifier.Enabled = False
        btnRechercheDetaillee.PerformClick()
        btnRechercher.PerformClick()
        remplirListCbo("province", "Tous", cboProvince, "")
        remplirListCbo("ville", "Tous", cboVille, " WHERE v.CodeProvince = p.CodeProvince AND p.nomProvince = '" & cboProvince.SelectedItem & "'")
        chkEntreprise.Checked = False
        chkActif.Checked = True
    End Sub

    Private Sub txtNbJours_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs)
        If Not IsNumeric(e.KeyChar) And e.KeyChar <> ControlChars.Back And e.KeyChar <> ControlChars.Tab Then
            e.Handled = True
        End If
    End Sub

    
    Private Sub chkDateDeb_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkDateDeb.CheckedChanged

    End Sub

    Private Sub chkDateFin_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
        dtFin.Enabled = chkDateDeb.Checked
    End Sub

    Private Sub txtNbJours_KeyPress1(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtNbJours.KeyPress
        If Not IsNumeric(e.KeyChar) And e.KeyChar <> ControlChars.Back And e.KeyChar <> ControlChars.Tab Then
            e.Handled = True
        End If
    End Sub

    Private Sub txtNbJours_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtNbJours.TextChanged
        If txtNbJours.Text <> Nothing Then
            dtFin.Value = dtDebut.Value.AddDays(CInt(txtNbJours.Text))
        End If
    End Sub


    Private Sub dtDebut_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtDebut.ValueChanged
        txtNbJours.Text = DateDiff(DateInterval.Day, dtDebut.Value, dtFin.Value)
        dtFin.MinDate = dtDebut.Value
    End Sub

    Private Sub dtFin_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtFin.ValueChanged
        txtNbJours.Text = DateDiff(DateInterval.Day, dtDebut.Value, dtFin.Value)
    End Sub

    Private Sub panRecherche_Paint(ByVal sender As System.Object, ByVal e As System.Windows.Forms.PaintEventArgs) Handles panRecherche.Paint

    End Sub

    Private Sub lstResultat_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstResultat.SelectedIndexChanged
     
    End Sub

    Private Sub btnAnnuler_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAnnuler.Click
        Me.Close()
    End Sub
End Class